package com.hns.can.service.enumrate;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.hns.can.service.entity.BaseDriverExamInfoEntity;
import com.hns.frame.enumrate.MsgType;
import com.hns.tool.pub.PubUtil;

public class ParseZhenBaoDrvAttExecl {
	private static Map<String,String> map = new HashMap<String,String>();
	
	//读取珍宝Execl
    public static List<BaseDriverExamInfoEntity> parseZhenBaoExcelToList(File file) throws Exception {
        Workbook wb = getWorkbook(file);
         return roadZhenBaoExecl(wb);
    }
    public static Workbook getWorkbook(File file) throws FileNotFoundException, IOException, Exception{
   	 Workbook wb = null;
   	 String fileName = file.getName();
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
        try {
        	wb =  new XSSFWorkbook(file);
        } catch (Exception ex) {
        	wb =  new HSSFWorkbook(new FileInputStream(file));
        	ex.printStackTrace();
        }
        return wb;
   }
	
	 public static List<BaseDriverExamInfoEntity> roadZhenBaoExecl(Workbook wb){
	    	Sheet sheet = null;
	        Row row = null;
	        Cell cell = null;
	        int count = 0;
	        List<BaseDriverExamInfoEntity> list = new ArrayList<BaseDriverExamInfoEntity>();
	        for(int n=0;n<wb.getNumberOfSheets();n++){
	        	sheet = wb.getSheetAt(n);
	        	count++;
	            for (int i = 9; i < sheet.getLastRowNum(); i++) {
	                row = sheet.getRow(i);
//	                System.out.println("行数:"+sheet.getLastRowNum());
//	                System.out.println("列数:"+row.getLastCellNum());
	                BaseDriverExamInfoEntity entity = new BaseDriverExamInfoEntity();
	                String cellValue = null;
	                for(int j = 0;j<row.getLastCellNum()-1;j++){
	                	cell = row.getCell(j);
	                	if(isMergedRegion(sheet,i,j)){
	                		cellValue = getMergedRegionValue(sheet,i,j).trim();
	                	}else{
	                		cellValue = getCellValue(cell).trim();
//	                		System.out.println(cellValue);
	                	}
	                	if(j<10){
	                		switch (j) {
	                        case 1:
	                        	//车牌号
	                        	entity.setCarId(cellValue);
	                            break;
	                        case 2:
	                        	//班时,临时存在创建人中
	                        	entity.setCrtUser(cellValue);
	                        case 4:
	                        	//驾驶员工号
	                        	entity.setDrvId(cellValue);
	                            break;
	                        case 5:
	                        	//签到时间1
	                        	if("00:00:00".equals(cellValue)){
	                        		break;
	                        	}
	                        	entity.setSignInTime(cellValue);
	                            break;
	                        case 6:
	                        	//签退时间1
	                        	if(entity.getSignInTime() != null){
	                        		entity.setSignOutTime(cellValue);
	                        	}
	                        	break;
	                        case 7:
	                        	//签到时间2
	                        	if("00:00:00".equals(cellValue)){
	                        		break;
	                        	}else if(entity.getSignInTime() != null){
	                        		//出现早晚班时, 签到时间1,2均有值，零时存在线路ID跟企业ID中
	                        		entity.setIconSkin(cellValue);
	                        		break;
	                        	}
	                        	entity.setSignInTime(cellValue);
	                            break;
	                        case 8:
	                        	//签退时间2
	                        	if("00:00:00".equals(cellValue)){
	                        		break;
	                        	}else if(entity.getSignOutTime() != null){
	                        		//出现早晚班时, 签到时间1,2均有值，零时存在线路ID跟企业ID中
	                        		entity.setRcrdTime(cellValue);
	                        		break;
	                        	}
	                        	entity.setSignOutTime(cellValue);
	                            break;
		                	}
		                }else{
		                	System.out.println("cell:"+cell);
		                	if(i == 9){
		                		map.put(j+"", cellValue);
		                		System.out.println(cellValue);
		                	}
		                	if(cellValue.length()>0){
		                		entity.setWorkDura(j+"");
//		                		System.out.println(cellValue+","+entity.getWorkDura());	
		                	}
		                }
	                }
	                list.add(entity);
	            }
	        }
			return list;
	    }
	 
	 /**  
	  * 判断指定的单元格是否是合并单元格  
	  * @param sheet  
	  * @param row  
	  * @param column  
	  * @return  
	  */  
	 public static boolean isMergedRegion(Sheet sheet , int row , int column){  
	     int sheetMergeCount = sheet.getNumMergedRegions();  
	     for(int i = 0 ; i < sheetMergeCount ; i++ ){  
	         CellRangeAddress ca = sheet.getMergedRegion(i);  
	         int firstColumn = ca.getFirstColumn();  
	         int lastColumn = ca.getLastColumn();  
	         int firstRow = ca.getFirstRow();  
	         int lastRow = ca.getLastRow();  
	           
	         if(row >= firstRow && row <= lastRow){  
	             if(column >= firstColumn && column <= lastColumn){  
	                 return true ;  
	             }  
	         }  
	     }  
	     return false ;  
	 }  
	 
	 /**  
	  * 获取合并单元格的值  
	  * @param sheet  
	  * @param row  
	  * @param column  
	  * @return  
	  */  
	 public static String getMergedRegionValue(Sheet sheet ,int row , int column){  
	     int sheetMergeCount = sheet.getNumMergedRegions();  
	     for(int i = 0 ; i < sheetMergeCount ; i++){  
	         CellRangeAddress ca = sheet.getMergedRegion(i);  
	         int firstColumn = ca.getFirstColumn();  
	         int lastColumn = ca.getLastColumn();  
	         int firstRow = ca.getFirstRow();  
	         int lastRow = ca.getLastRow();  
	         if(row >= firstRow && row <= lastRow){  
	             if(column >= firstColumn && column <= lastColumn){  
	                 Row fRow = sheet.getRow(firstRow);  
	                 Cell fCell = fRow.getCell(firstColumn);  
	                 return getCellValue(fCell) ;  
	             }  
	         }  
	     }  
	     return null ;  
	 }  
	/**
     * @param cell
     * @return
     * @author:JeffXu
     * @email:835264047@qq.com
     * @创建日期:2014-8-14
     * @功能说明：得到每个表格的值
     */
    private static String getCellValue(Cell cell) {
        String value = null;
        DecimalFormat df = new DecimalFormat("0");// 格式化 number String // 字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
        DecimalFormat nf = new DecimalFormat("#");// 格式化数字
        if(cell != null){
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                        value = df.format(cell.getNumericCellValue());
                    } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                        value = nf.format(cell.getNumericCellValue());
                    } else if (cell.getCellStyle().getDataFormat() == 22){
                        value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                    } else if(HSSFDateUtil.isCellDateFormatted(cell)){
                    		// 日期  
                    	if (cell.getCellStyle().getDataFormat() == 22) {  
                    		value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                        } else if(cell.getCellStyle().getDataFormat() == 21 || cell.getCellStyle().getDataFormat() == 20){
                        	sdf = new SimpleDateFormat("HH:mm:ss");
                        }else{
                            sdf = new SimpleDateFormat("yyyy-MM-dd");  
                        }    
                        Date date = cell.getDateCellValue();  
                        value =  sdf.format(date);
                    }else {
                    	double num = cell.getNumericCellValue();
                    	String temp = cell.getCellStyle().getDataFormatString();
                    	DecimalFormat format = new DecimalFormat();
                    	if (temp.equals("General")) {  
                            format.applyPattern("#");  
                        } 
                    	value = format.format(num);
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                default:
                    value = cell.toString();
            }
        }else{
            value = "";
        }
        return value;
    }
    
	   	
	 public static Map<String, String> getMap() {
		return map;
	}
	public static void setMap(Map<String, String> map) {
		ParseZhenBaoDrvAttExecl.map = map;
	}
//	public static void main(String[] args) throws Exception {
//		File file = new File("C:/Users/Chenl/Desktop/1.xlsx");
//		List<BaseDriverExamInfoEntity> list = parseZhenBaoExcelToList(file);
//		int count = 0;
//    	Map<String,String> map = ParseZhenBaoDrvAttExecl.getMap();
//    	for(int i = 2;i<list.size();i++){
//    		count++;
//    		BaseDriverExamInfoEntity entity = list.get(i);
//    		//签到时间
//    		//取出日期
//    		String time = entity.getSignInTime();
//    		if(time == null){
//    			continue;
//    		}
//    		String num = map.get(entity.getWorkDura());
//    		String SignInTime = getSignTime(num,time);
//    		//签退时间
//    		time = entity.getSignOutTime();
//    		if(time == null){
//    			continue;
//    		}
//    		String SignOutTime = getSignTime(num,time);
//    		System.out.println(count+","+SignInTime+","+SignOutTime);
//    	}
//	}
	
	public static String getSignTime(String num,String time) throws Exception{
		SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy年MM月dd日 HH:mm:ss");
    	SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    	String SignTime = num + " "+ time;
		Date date = sdf1.parse(SignTime);
		SignTime = sdf2.format(date);
		return SignTime;
	}
}
